package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.util.ArrayList;

import model.EmployeeJob;

public class DBEmployeeJob implements IFEmployeeJob
{
	private Connection con;

	public DBEmployeeJob()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createEmployeeJob(EmployeeJob job)
	{
		int rc = -1;

		String query = "SELECT employee_job_id FROM employee_job WHERE employee_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createEmployeeJob", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, job.getJobName());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO employee_job(employee_job_name) VALUES(?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createEmployeeJob", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, job.getJobName());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createEmployeeJob - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createEmployeeJob - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateEmployeeJob(EmployeeJob job)
	{
		int rc = -1;

		String query = "SELECT employee_job_id FROM employee_job WHERE employee_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createEmployeeJob", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, job.getJobName());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE employee_job SET employee_job_name = ? WHERE employee_job_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateEmployeeJob", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, job.getJobName());
					ps.setInt(2, job.getJobID());

					rc = ps.executeUpdate();

				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateEmployeeJob - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createEmployeeJob - SELECT: " + e);
				e.printStackTrace();
			}
		}
		return rc;
	}

	@Override
	public EmployeeJob findEmployeeJobByName(String jobName)
	{
		EmployeeJob newEmployeeJob = new EmployeeJob();

		String query = "SELECT * FROM employee_job WHERE employee_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllEmployeeJobs", query);

		try 
		{	
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, jobName);

			ResultSet results = ps.executeQuery();

			while(results.next())
				newEmployeeJob = buildEmployeeJobObject(results);

			ps.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - findEmployeeJobByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newEmployeeJob;
	}

	@Override
	public ArrayList<EmployeeJob> printAllEmployeeJobs()
	{
		ArrayList<EmployeeJob> EmployeeJobList = new ArrayList<EmployeeJob>();

		String query = "SELECT * FROM employee_job ORDER BY employee_job_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllEmployeeJobs", query);

		try 
		{	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);

			ResultSet results = stmt.executeQuery(query);

			while(results.next())
			{
				EmployeeJob newEmployeeJob = buildEmployeeJobObject(results);

				EmployeeJobList.add(newEmployeeJob);
			}

			stmt.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllEmployeeJobs - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return EmployeeJobList;
	}

	private EmployeeJob buildEmployeeJobObject(ResultSet results) throws SQLException
	{
		EmployeeJob newEmployeeJob = new EmployeeJob();

		newEmployeeJob.setJobID(results.getInt("employee_job_id"));
		newEmployeeJob.setJobName(results.getString("employee_job_name"));

		return newEmployeeJob;
	}
}